<?php


class DB{
  var $user="yycanada_test";
  var $password="test123";
  var $database="yycanada_test";
  function DB(){
    $link=mysql_connect('localhost',$this->user,$this->password);
    if (!$link) {
      die('Not connected : ' . mysql_error());
    }
    mysql_query("SET NAMES utf8");
    // make foo the current db
    $db_selected = mysql_select_db('yycanada_test', $link);
    if (!$db_selected) {
      die ('Can\'t use '.$database.' : ' . mysql_error());
    }
    $this->CreateTablesIfNotExist();
    return $link;
  }

  function CreateTablesIfNotExist(){
    $this->CreateEmployeeTablesIfNotExists();
    $this->CreateAttendanceTablesIfNotExists();
    $this->CreateAttendancePeriodTablesIfNotExists();

  }

  function CreateEmployeeTablesIfNotExists(){
    $query=" 
CREATE TABLE IF NOT EXISTS `yycanada_test`.`employee` (
`eid` INT( 11 ) NOT NULL ,
`name` VARCHAR( 32 ) NOT NULL,
PRIMARY KEY ( `eid` ) NOT NULL AUTO_INCREMENT
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci 
";
    mysql_query($query);
  }
  
  function CreateAttendanceTablesIfNotExists(){
    $query="
CREATE TABLE `yycanada_test`.`attendance` (
`eid` INT( 11 ) NOT NULL ,
`date` DATE NOT NULL ,
`late` VARCHAR( 5 ) NOT NULL DEFAULT '0',
`leave` VARCHAR( 5 ) NOT NULL DEFAULT '0',
`paid_holiday` VARCHAR( 5 ) NOT NULL DEFAULT '0',
`sick_leave` VARCHAR( 5 ) NOT NULL DEFAULT '0',
`overtime` VARCHAR( 5 ) NOT NULL DEFAULT '0',
`business_trip` VARCHAR( 5 ) NOT NULL DEFAULT '0',
PRIMARY KEY ( `eid` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci 
";
    mysql_query($query);
  }
  
  function CreateAttendancePeriodTablesIfNotExists(){
  }

}


class Employee{
  var $name;
  var $employee_id;
  var $has_contract;
  var $contract;
  function Employee(){
  }
}

class Employees{
  var $db;
  var $employees;
  function Employees(){
    $this->db = new DB();
    $this->employees=array();
    if (!$this->db) {
      die("Cannot connect to the database"); 
    }
  }

  function GetAll(){
    $query="
SELECT eid, name FROM `employee`
WHERE 1
";
    $r = mysql_query($query);
    if (!$r)  { 
      print("Query failed or return empty result"); 
    }
    else{
      while ($row = mysql_fetch_array($r)) {
        $employee=new Employee();
        $employee->name = $row["name"];
        $employee->eid = $row["eid"];
        array_push($this->employees, $employee);
      }
    }
    return $this->employees;
  }

  function GetAllWithContract(){
    $query="
SELECT e.eid as eid, e.name as name, c.title as contract_title, c.from as contract_from, c.to as contract_to 
FROM `employee` e LEFT JOIN contract c ON e.eid=c.eid
";
    $r = mysql_query($query);
    if (!$r)  { 
      print("Query failed or return empty result"); 
    }
    else{
      while ($row = mysql_fetch_array($r)) {
        $employee=new Employee();
        $employee->name = $row["name"];
        $employee->eid = $row["eid"];
        if(is_null($row["contract_title"])){
          $employee->has_contract= "N";
        }else{
          $employee->has_contract= "Y";
        }
        $employee->has_contract = ($row["contract_title"] ? "1" : "0");
        $contract =New Contract();
        if ($employee->has_contract){
          $contract->title = $row["contract_title"];
          $contract->from = $row["contract_from"];
          $contract->to = $row["contract_to"];
          $contract->validity = $contract->CheckValidity();
        }
        $employee->contract = $contract;
        array_push($this->employees, $employee);
      }
    }
    return $this->employees;
  }


  function PrintAll(){
    $this->GetAllWithContract();
    printf("<table border=1><tbody>\n");
    printf("<tr align=center> <th>%s</th> <th>%s</th> <th>%s</th> <th>%s</th> <th>%s</th> <th>%s</th> <th>%s</th> </tr>\n", 
                              '[ID]', '[NAME]','[HAS CONTRACT]','[CONTRACT TITLE]','[FROM]','[TO]','[VALIDITY]');  
    foreach ($this->employees as $employee) {
      printf("<tr align=center> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> </tr>\n", 
        $employee->eid, $employee->name, $employee->has_contract, $employee->contract->title, $employee->contract->from, $employee->contract->to, $employee->contract->validity );  
    }
    printf("</tbody></table>\n");
  }
}

class Attendance{
  var $employee_id;
  var $date;
  var $late;
  var $paid_holiday;
  var $leave;
  var $sick_leave;
  var $overtime;
  var $business_trip;
  function Attendance(){
  }
}


// the period includes the from date, does not include the to date.
// do not need a table, just calculate all the results
class AttendancePeriod{
  var $from;
  var $to;
  var $calendar_days;
  var $working_days;
  function AttendancePeriod($f,$t){
    $this->from = $f;
    $this->to = $t;
    $this->CalculateCalendarDays();
    $this->CalculateWorkingDays();
  }

  function CalculateCalendarDays(){
  }
  
  function CalculateWorkingDays(){
  }

}

Class Contract{
  var $title;
  var $from;
  var $to;
  var $validity;

  function Contract(){
    $this->title = ""; 
    $this->from = "";
    $this->to = "";
    $this->validity = "N/A";
  }

  function DaysBetween($date1, $date2){
    $d1 = explode("-", $date1);
    $d2 = explode("-", $date2);
  
    $year1 = $d1[0];
    $month1 = $d1[1];
    $day1 = $d1[2];

    $year2 =  $d2[0];
    $month2 = $d2[1];
    $day2 = $d2[2];
   
    $deadline1 = mktime(0, 0, 0, $month1, $day1, $year1, 0);
    $deadline2 = mktime(0, 0, 0, $month2, $day2, $year2, 0);

    /* If you dont put 0 at last parameter both mktime calls, and
       a Daylight Saving Time is not specified,
       a float days number can be returned. Is better to pass 0
       in both mktimes and round the result: */
   
    $res = round( ($deadline2 - $deadline1) / (60 * 60 * 24) );
    return $res;
  }
  
  function CheckValidity(){
    if ($this->from == "0000-00-00"|| $this->to == "0000-00-00") return "N/A";
    if ($this->to == "") {
      if($this->from == "") return "N/A";   //no record
      else return "OK";                     //unlimited contract
    }
    $days = $this->DaysBetween(date("Y-m-d"),$this->to);
    if ($days<=0){
      return "Expired";
    }elseif($days<30){
      return "Near";
    }else{
      return "OK";
    } 
  }

  function CreateContractTablesIfNotExists(){
    $query="
 CREATE TABLE `yycanada_test`.`contract` (
`cid` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 128 ) NOT NULL ,
`from` DATE NOT NULL DEFAULT '0000-00-00',
`to` DATE NOT NULL DEFAULT '0000-00-00',
`eid` INT( 11 ) NOT NULL DEFAULT '0',
`attachment` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `cid` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci 
";
    mysql_query($query);
  }

}


?>
